Option Explicit

Public IsMonitoring As Boolean
Public TargetFilePath As String

#If VBA7 Then
    Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Declare PtrSafe Function GetWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal wCmd As Long) As LongPtr
    Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Declare PtrSafe Function GetDlgItem Lib "user32" (ByVal hDlg As LongPtr, ByVal nIDDlgItem As Long) As LongPtr
#Else
    Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
    Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
    Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Declare Function GetDlgItem Lib "user32" (ByVal hDlg As Long, ByVal nIDDlgItem As Long) As Long
#End If

Const GW_CHILD = 5
Const GW_HWNDNEXT = 2
Const WM_COMMAND = &H111

Sub StartMonitoring()
    If IsMonitoring Then MsgBox "Already monitoring!", vbInformation: Exit Sub
    TargetFilePath = Trim(ThisWorkbook.Sheets(1).Range("A3").Value)
    If TargetFilePath = "" Or Dir(TargetFilePath) = "" Then MsgBox "Invalid path in A3!", vbCritical: Exit Sub
    On Error Resume Next: ThisWorkbook.Sheets(1).Hyperlinks.Delete: On Error GoTo 0
    ThisWorkbook.Sheets(1).Hyperlinks.Add Anchor:=ThisWorkbook.Sheets(1).Range("A3"), Address:=TargetFilePath, TextToDisplay:=TargetFilePath
    IsMonitoring = True
    ThisWorkbook.Sheets(1).Range("A2").Value = "Monitoring..."
    MsgBox "Monitoring: " & TargetFilePath, vbInformation
    Application.OnTime Now + TimeSerial(0, 0, 5), "CheckAndOpen"
End Sub

Sub CheckAndOpen()
    If Not IsMonitoring Then Exit Sub
    If IsFileWritable(TargetFilePath) Then
        ThisWorkbook.Sheets(1).Range("A2").Value = "Opening..."
        Call SafeFollowHyperlink(TargetFilePath)
        ThisWorkbook.Sheets(1).Range("A2").Value = "Opened in write mode"
        MsgBox "SUCCESS: File opened in write mode!", vbInformation
        IsMonitoring = False
    Else
        ThisWorkbook.Sheets(1).Range("A2").Value = "In use, retry in 5s..."
        Application.OnTime Now + TimeSerial(0, 0, 5), "CheckAndOpen"
    End If
End Sub

Sub SafeFollowHyperlink(path As String)
    On Error Resume Next
    ThisWorkbook.FollowHyperlink path
    Application.Wait Now + TimeSerial(0, 0, 1)
    Dim hwnd As LongPtr, btnHwnd As LongPtr
    hwnd = FindWindow(vbNullString, "Microsoft Excel Security Notice")
    If hwnd <> 0 Then
        btnHwnd = GetDlgItem(hwnd, 6)
        If btnHwnd = 0 Then btnHwnd = GetDlgItem(hwnd, &H2)
        If btnHwnd <> 0 Then SendMessage btnHwnd, WM_COMMAND, 0, 0
    End If
    On Error GoTo 0
End Sub

Function IsFileWritable(path As String) As Boolean
    Dim ff As Integer
    On Error Resume Next
    ff = FreeFile()
    Open path For Binary Access Write Lock Write As #ff
    If Err.Number = 0 Then IsFileWritable = True: Close #ff
    On Error GoTo 0
End Function

Sub StopMonitoring()
    IsMonitoring = False
    ThisWorkbook.Sheets(1).Range("A2").Value = "Stopped."
    MsgBox "Monitoring stopped.", vbInformation
End Sub